package com.jweb.framework.id;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jweb.framework.core.util.StringUtils;

/**
 * �޸��ˣ� Thomas
 * �޸����ݣ����� 
 * ��˵�������в�����
 */
public class SequenceTable {

    private static final Logger logger = LoggerFactory.getLogger(SequenceTable.class); //
    private DataSource dataSource; //
    private String tableName = "seq_no"; //
    private String idColume = "id"; //
    private String typeColume = "type"; //
    private String insertSql; //
    private String updateSql; // 
    private String selectSql; // 
    private String update0Sql; // 
    private String corpidColume = "";

    private TimeService timeService = new TimeServiceImpl(); // 

    public void setCorpidColume(String corpid) {
        this.corpidColume = corpid;
    }

    public void setTableName(String tn) {
        this.tableName = tn;
    }

    public void setIdColume(String ic) {
        this.idColume = ic;
    }

    public void setTypeColume(String tc) {
        this.typeColume = tc;
    }

    public void setDataSource(DataSource ds) {
        this.dataSource = ds;
    }

    public void setTimeService(TimeService timeService) {
        this.timeService = timeService;
    }

    protected TimeService getTimeService() {
        return this.timeService;
    }

    /**
     * ��ʼ
     */
    public void init() {
        Connection conn = null;
        Statement st = null;
        try {
            conn = this.dataSource.getConnection();
            st = conn.createStatement();
            StringBuffer sql = new StringBuffer();
            sql.append("create table " + this.tableName + "(");
            if(StringUtils.isNotEmpty(this.corpidColume)){
                sql.append(this.corpidColume + " varchar2(32) not null, ");
            }
            sql.append(this.typeColume + " varchar2(2) not null, ");
            if(StringUtils.isNotEmpty(this.corpidColume)){
            	sql.append(this.idColume + " number , datetime date, primary key(" + this.typeColume + "," + this.corpidColume + "))");
            }
            else {
            	sql.append(this.idColume + " number , datetime date, primary key(" + this.typeColume + "))");
            }
             //create table seq_no(type varchar2(2) not null, id number , datetime date, primary key(type))
            st.execute(sql.toString());
            st.close();
        } catch (SQLException e) {
            logger.warn("cannot create sequence table , " + e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {

            }
        }
    }

    public void reset() {
        try {
            Connection conn = this.dataSource.getConnection();
            Statement st = null;
            boolean flag = conn.getAutoCommit();

            try {
                conn.setAutoCommit(false); //�����Զ��ύ
                st = conn.createStatement();
                StringBuffer sql = new StringBuffer();
                sql.append("update table " + this.tableName + " set " + this.idColume + "=0 , set datetime='" + new Timestamp(this.timeService.currentTimeMillis()) + "'");
                st.execute(sql.toString());
                conn.commit();

            } catch (Exception e) {
                conn.rollback();
                logger.debug("cannot reset sequence table , " + e);
            } finally {
                conn.setAutoCommit(flag);
                conn.close();
            }
        } catch (SQLException ex) {
            logger.warn("cannot reset sequence table , " + ex);
        }
    }

    protected Connection getConnection() throws SQLException {
        return this.dataSource.getConnection();
    }

    protected long[] doSelect(Connection conn, String type, boolean flag, String corpId) throws SQLException {
        long l = this.timeService.currentTimeMillis(); // current time
        long[] resArr = { -1L, -1L };
        PreparedStatement pst = conn.prepareStatement(getSelectSql()); // select id, datetime from seq_no where type=?
        ResultSet rs = null;
        try {
            pst.setString(1, type);
            if (StringUtils.isNotEmpty(corpId)) {
                pst.setString(2, corpId);
            }
            rs = pst.executeQuery();
            if (rs.next()) {

                resArr[0] = rs.getLong(1);
                Timestamp timestamp = rs.getTimestamp(2);
                if ((timestamp != null) && (timestamp.getTime() > l)) {
                    resArr[1] = timestamp.getTime();
                } else {
                    resArr[1] = l;
                    if ((flag) && (this.timeService.isCutoff(5, l, timestamp.getTime()))) { // ���ڲ�
                        int k = doRest(conn, type, l, resArr[0], corpId);
                        if (k == 1) {
                            resArr[0] = 0L;
                        } else {
                            return doSelect(conn, type, flag, corpId);
                        }
                    }
                }
            } else {
                resArr[1] = l;
                logger.warn("cannot get a id due to no initialized.");
            }

        } catch (SQLException e) {
            throw e;
        } finally {
            rs.close();
            pst.close();
        }

        return resArr;
    }

    protected void doInsert(Connection conn, String type, long id, long tim, String corpId) throws SQLException {
        PreparedStatement pst = conn.prepareStatement(getInsertSql());
        try {
            pst.setString(1, type);
            pst.setLong(2, id);
            pst.setTimestamp(3, new Timestamp(tim));
            if (StringUtils.isNotEmpty(corpId)) {
                pst.setString(4, corpId);
            }

            int count = pst.executeUpdate();
            if (count != 1) {
                throw new SQLException("sequence table insert fail.");
            }

        } catch (SQLException e) {
            throw new SQLException("sequence table insert fail.");
        } finally {
            pst.close();
        }
    }

    protected int doRest(Connection conn, String type, long tim, long id, String corpId) throws SQLException {
        PreparedStatement pst = conn.prepareStatement(getUpdate0Sql()); // update seq_no set id=0 , datetime=?  where type=? and id=?
        int count = 0;
        try {
            pst.setTimestamp(1, new Timestamp(tim));
            pst.setString(2, type);
            pst.setLong(3, id);
            if (StringUtils.isNotEmpty(corpId)) {
                pst.setString(4, corpId);
            }
            count = pst.executeUpdate();
        } catch (SQLException e) {
            logger.warn("could not reset type " + type + ", " + e);
            throw e;
        } finally {
            pst.close();
        }
        return count;
    }

    protected int doupdate(Connection conn, String type, int sequ, long tim, long id, String corpId) throws SQLException {
        PreparedStatement pst = conn.prepareStatement(getUpdateSql()); //update seq_no set id=id+?, datetime=? where type=? and id=?
        int count = 0;
        try {
            pst.setLong(1, sequ);
            pst.setTimestamp(2, new Timestamp(tim));
            pst.setString(3, type);
            pst.setLong(4, id);
            if (StringUtils.isNotEmpty(corpId)) {
                pst.setString(5, corpId);
            }
            count = pst.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            pst.close();
        }
        return count;
    }

    private String getSelectSql() { //

        if (StringUtils.isEmpty(this.selectSql)) {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty(this.corpidColume)) {
                sb.append("select " + this.idColume + ", datetime from " + this.tableName + " where " + this.typeColume + "=?");
            } else {
                sb.append( "select " + this.idColume + ", datetime from " + this.tableName + " where " + this.typeColume + "=? and " + this.corpidColume + "=?");
            }
            this.selectSql = sb.toString();
        }
        return this.selectSql;
    }

    private String getUpdate0Sql() { //
        if (StringUtils.isEmpty(this.update0Sql)) {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty(this.corpidColume)) {
                sb.append("update " + this.tableName + " set " + this.idColume + "=0 , datetime=?  where " + this.typeColume + "=? and " + this.idColume + "=?");
            } else {
                sb.append("update " + this.tableName + " set " + this.idColume + "=0 , datetime=?  where " + this.typeColume + "=? and " + this.idColume + "=? and " + this.corpidColume + "=?");
            }
            this.update0Sql = sb.toString();
        }
        return this.update0Sql;
    }

    private String getUpdateSql() {//
        if (StringUtils.isEmpty(this.updateSql)) {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty(this.corpidColume)) {
                sb.append("update " + this.tableName + " set " + this.idColume + "=" + this.idColume + "+?, datetime=? where " + this.typeColume + "=? and " + this.idColume + "=?");
            } else {
                sb.append("update " + this.tableName + " set " + this.idColume + "=" + this.idColume + "+?, datetime=? where " + this.typeColume + "=? and " + this.idColume + "=? and " + this.corpidColume + "=?");
            }
            this.updateSql = sb.toString();
        }
        return this.updateSql;
    }

    private String getInsertSql() {//
        if (StringUtils.isEmpty(this.insertSql)) {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty(this.corpidColume)) {
                sb.append("insert into " + this.tableName + " (" + this.typeColume + "," + this.idColume + ", datetime) values(?,?,?)");
            } else {
                sb.append("insert into " + this.tableName + " (" + this.typeColume + "," + this.idColume + ", datetime," + this.corpidColume + ") values(?,?,?,?)");
            }
            this.insertSql = sb.toString();
        }
        return this.insertSql;
    }

}
